package org.snail.excel.annotation;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.snail.excel.common.ExcelDataFormatter;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

//这个是用于解析大容量Excel
public class ExampleEventUserModel {

    private static Logger logger = Logger.getLogger(ExampleEventUserModel.class);

    private static StylesTable stylesTable;

    /**
     * 处理一个sheet
     * @param filename
     * @throws Exception
     */
    public  <T> List<T>  processOneSheet(String filename, String sheetId, int startRow, Class<T> t) throws Exception {
        List<T> resultList = new ArrayList<T>();

        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        stylesTable = r.getStylesTable();
        SharedStringsTable sst = r.getSharedStringsTable();

        XMLReader parser = fetchSheetParser(sst, resultList, startRow, t);

        // Seems to either be rId# or rSheet#
        InputStream sheet2 = r.getSheet("rId" + sheetId);
        InputSource sheetSource = new InputSource(sheet2);
        parser.parse(sheetSource);
        sheet2.close();

        return resultList;
    }

    /**
     * 获取解析器
     * @param sst
     * @return
     * @throws SAXException
     */
    public <T> XMLReader fetchSheetParser(SharedStringsTable sst, List<T> resultList, int startRow, Class<T> t) throws SAXException {
        XMLReader parser =
                XMLReaderFactory.createXMLReader(
                        "org.apache.xerces.parsers.SAXParser"
                );
        ContentHandler handler = new SheetHandler(sst, resultList, startRow, t);
        parser.setContentHandler(handler);
        return parser;
    }

    /**
     * 自定义解析处理器
     * See org.xml.sax.helpers.DefaultHandler javadocs
     */
    private static class SheetHandler <T> extends DefaultHandler {

        //传入参数
        private SharedStringsTable sst;
        private List<T> resultList;
        private int startRow;
        private Class<T> t;

        private String lastContents;
        private boolean nextIsString;

        private Map<Integer, Object> rowMap = new HashMap<Integer, Object>();

        //当前列
        private int curCol = 0;
        // 当前行
        private int curRow = 0;

        //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
        private String preRef = null, ref = null;
        //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
        private String maxRef = null;

        private CellDataType nextDataType = CellDataType.SSTINDEX;
        private final DataFormatter formatter = new DataFormatter();
        private short formatIndex;
        private String formatString;

        //用一个enum表示单元格可能的数据类型
        enum CellDataType{
            BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
        }

        private SheetHandler(SharedStringsTable sst, List<T> resultList, int startRow, Class<T> t) {
            this.sst = sst;
            this.resultList = resultList;
            this.startRow = startRow;
            this.t = t;
        }

        /**
         * 解析一个element的开始时触发事件
         */
        public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {

            // c => cell
            if(name.equals("c")) {
                //前一个单元格的位置
                if(preRef == null){
                    preRef = attributes.getValue("r");
                }else{
                    preRef = ref;
                }
                //当前单元格的位置
                ref = attributes.getValue("r");

                this.setNextDataType(attributes);

                // Figure out if the value is an index in the SST
                String cellType = attributes.getValue("t");
                if(cellType != null && cellType.equals("s")) {
                    nextIsString = true;
                } else {
                    nextIsString = false;
                }

            }
            // Clear contents cache
            lastContents = "";
        }

        /**
         * 根据element属性设置数据类型
         * @param attributes
         */
        public void setNextDataType(Attributes attributes){

            nextDataType = CellDataType.NUMBER;
            formatIndex = -1;
            formatString = null;
            String cellType = attributes.getValue("t");
            String cellStyleStr = attributes.getValue("s");
            if ("b".equals(cellType)){
                nextDataType = CellDataType.BOOL;
            }else if ("e".equals(cellType)){
                nextDataType = CellDataType.ERROR;
            }else if ("inlineStr".equals(cellType)){
                nextDataType = CellDataType.INLINESTR;
            }else if ("s".equals(cellType)){
                nextDataType = CellDataType.SSTINDEX;
            }else if ("str".equals(cellType)){
                nextDataType = CellDataType.FORMULA;
            }
            if (cellStyleStr != null){
                int styleIndex = Integer.parseInt(cellStyleStr);
                XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                formatIndex = style.getDataFormat();
                formatString = style.getDataFormatString();
                if ("m/d/yy" == formatString){
                    nextDataType = CellDataType.DATE;
                    //full format is "yyyy-MM-dd hh:mm:ss.SSS";
                    formatString = "yyyy-MM-dd";
                }
                if (formatString == null){
                    nextDataType = CellDataType.NULL;
                    formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
                }
            }
        }

        /**
         * 解析一个element元素结束时触发事件
         */
        public void endElement(String uri, String localName, String name)
                throws SAXException {
            // Process the last contents as required.
            // Do now, as characters() may be called more than once
            if(nextIsString) {
                int idx = Integer.parseInt(lastContents);
                lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                nextIsString = false;
            }

            // v => contents of a cell
            // Output after we've seen the string contents
            if (name.equals("v")) {
                String value = this.getDataValue(lastContents.trim(), "");
                //补全单元格之间的空单元格
                if(!ref.equals(preRef)){
                    int len = countNullCell(ref, preRef);
                    for(int i=0;i<len;i++){
                        rowMap.put(curCol, "");
                        curCol++;
                    }
                }
                rowMap.put(curCol, value);
                curCol++;
            } else {
                //如果标签名称为 row，这说明已到行尾
                if (name.equals("row")) {

                    //补全一行尾部可能缺失的单元格
                    if(maxRef != null){
                        int len = countNullCell(maxRef, ref);
                        for(int i=0;i<=len;i++){
                            rowMap.put(curCol, "");
                            curCol++;
                        }
                    }

                    //一行解析结束后，添加到集合中，并清空VO
                    if(curRow>=startRow) {
                        T t = matchRow2Bean(rowMap, this.t);
                        if(null!=t) {
                            resultList.add(t);
                        }
                    }

                    //一行的末尾重置一些数据，行增加，列重新从0开始
                    rowMap.clear();
                    curRow++;
                    curCol = 0;
                    preRef = null;
                    ref = null;
                }
            }
        }

        private T matchRow2Bean(Map<Integer, Object> rowMap, Class<T> t) {
            {
                T newInstance = null;
                try {
                    if(t != null) {
                        //判断此类为Excel Bean 对象
                        newInstance =  t.newInstance();
                        ExcelBean excelBean = newInstance.getClass().getAnnotation(ExcelBean.class);
                        if(excelBean!=null && excelBean.isExcelBean()) {
                            Field[] fields = newInstance.getClass().getDeclaredFields();

                            //遍历字段，转换为Bean对象XSSFWorkbook
                            for (Field field:fields) {
                                field.setAccessible(true);
                                ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                                if(excelColumn!=null && excelColumn.skip()!=true) {
                                    //1、获取单元格的值
                                    if(excelColumn.position()>=0 ) {
                                        Object obj = rowMap.get(excelColumn.position());
                                        if(obj!=null) {

                                            //2、获取单元格的转换器
                                            if(null!=obj) {
                                                Class edfCls = excelColumn.dataFormatter();
                                                ExcelDataFormatter excelDataFormatter = null;
                                                if (null != edfCls && !(edfCls instanceof Object)) {
                                                    excelDataFormatter = (ExcelDataFormatter) edfCls.newInstance();
                                                }

                                                //有值时，进行转换，给Bean对象设置值
                                                translateCell2Field(newInstance, obj, field, excelDataFormatter, obj);
                                            }
                                        }
                                    }
                                }
                            }
                        } else {
                            newInstance = null;
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
                return newInstance;
            }
        }

        /**
         * 给对象各个属性设置值
         * @param t
         * @param value
         * @param field
         * @param edf
         * @param <T>
         * @return
         */
        private <T> T translateCell2Field(T t, Object value,Field field, ExcelDataFormatter edf, Object obj) {
            //1、转换器不为空时，进行数据转换
            if(null != edf) {
                value = edf.translate(obj, value);
            }
            try {
                if(field.getType().equals(Date.class)) {
                    Date DateValue = parseDate((String)value,"yyyy/MM/dd");
//                if(value.getClass().equals(Date.class)) {
                    field.set(t, DateValue);
//                }
                } else if(field.getType().equals(String.class)) {
                    if(value.getClass().equals(String.class)) {
                        field.set(t, value);
                    } else {
                        field.set(t, value.toString());
                    }
                } else if(field.getType().equals(Long.class)) {
                    if(value.getClass().equals(Long.class)) {
                        field.set(t, value);
                    } else {
                        field.set(t, Long.parseLong(value.toString()));
                    }
                } else if(field.getType().equals(Integer.class)) {
                    if(value.getClass().equals(Integer.class)) {
                        field.set(t, value);
                    } else {
                        field.set(t, Integer.parseInt(value.toString()));
                    }
                } else if(field.getType().equals(BigDecimal.class)) {
                    if(value.getClass().equals(BigDecimal.class)) {
                        field.set(t, value);
                    } else {
                        field.set(t, BigDecimal.valueOf(Double.parseDouble(value.toString())));
                    }
                } else if(field.getType().equals(Boolean.class)) {
                    if(value.getClass().equals(Boolean.class)) {
                        field.set(t, value);
                    } else {
                        field.set(t, Boolean.parseBoolean(value.toString()));
                    }
                } else if(field.getType().equals(Float.class)) {
                    if(value.getClass().equals(Float.class)) {
                        field.set(t, value);
                    } else {
                        field.set(t, Float.parseFloat(value.toString()));
                    }
                } else if(field.getType().equals(Double.class)) {
                    if(value.getClass().equals(Double.class)) {
                        field.set(t, value);
                    } else {
                        field.set(t, Double.parseDouble(value.toString()));
                    }
                }
            } catch (Exception e) {
                logger.error(e.getStackTrace(),e);
            }
            return t;
        }

        private Date parseDate(String str, String parsePattern) throws ParseException {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(parsePattern);
            return simpleDateFormat.parse(str);
        }

        /**
         * 根据数据类型获取数据
         * @param value
         * @param thisStr
         * @return
         */
        public String getDataValue(String value, String thisStr)

        {
            switch (nextDataType)
            {
                //这几个的顺序不能随便交换，交换了很可能会导致数据错误
                case BOOL:
                    char first = value.charAt(0);
                    thisStr = first == '0' ? "FALSE" : "TRUE";
                    break;
                case ERROR:
                    thisStr = "\"ERROR:" + value.toString() + '"';
                    break;
                case FORMULA:
                    thisStr = '"' + value.toString() + '"';
                    break;
                case INLINESTR:
                    XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                    thisStr = rtsi.toString();
                    rtsi = null;
                    break;
                case SSTINDEX:
                    String sstIndex = value.toString();
                    thisStr = value.toString();
                    break;
                case NUMBER:
                    if (formatString != null){
                        thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                    }else{
                        thisStr = value;
                    }
                    thisStr = thisStr.replace("_", "").trim();
                    break;
                case DATE:
                    try{
                        thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
                    }catch(NumberFormatException ex){
                        thisStr = value.toString();
                    }
                    thisStr = thisStr.replace(" ", "");
                    break;
                default:
                    thisStr = "";
                    break;
            }
            return thisStr;
        }

        /**
         * 获取element的文本数据
         */
        public void characters(char[] ch, int start, int length)
                throws SAXException {
            lastContents += new String(ch, start, length);
        }

        /**
         * 计算两个单元格之间的单元格数目(同一行)
         * @param ref
         * @param preRef
         * @return
         */
        public int countNullCell(String ref, String preRef){
            //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
            String xfd = ref.replaceAll("\\d+", "");
            String xfd_1 = preRef.replaceAll("\\d+", "");

            xfd = fillChar(xfd, 3, '@', true);
            xfd_1 = fillChar(xfd_1, 3, '@', true);

            char[] letter = xfd.toCharArray();
            char[] letter_1 = xfd_1.toCharArray();
            int res = (letter[0]-letter_1[0])*26*26 + (letter[1]-letter_1[1])*26 + (letter[2]-letter_1[2]);
            return res-1;
        }

        /**
         * 字符串的填充
         * @param str
         * @param len
         * @param let
         * @param isPre
         * @return
         */
        String fillChar(String str, int len, char let, boolean isPre){
            int len_1 = str.length();
            if(len_1 <len){
                if(isPre){
                    for(int i=0;i<(len-len_1);i++){
                        str = let+str;
                    }
                }else{
                    for(int i=0;i<(len-len_1);i++){
                        str = str+let;
                    }
                }
            }
            return str;
        }
    }



}
